package dao;

import model.Course;
import model.Score;
import model.Student;
import utils.DBUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author tongchen
 * @create 2022-12-29 11:22
 */
public class ScoreDao {
    //创建变量
    Connection connection=null;
    PreparedStatement statement=null;
    ResultSet resultSet=null;
public int newScore(Score score){
    //创建连接
    try {
        connection = DBUtils.getConnection();
        //sql语句
        String sql="insert into score (score,student_id,course_id)values(?,?,?)";
        //创建执行器
        statement= connection.prepareStatement(sql);
        statement.setBigDecimal(1,score.getScore());
        statement.setInt(2,score.getStudentId());
        statement.setInt(3,score.getCouseId());
        //执行sql
        int i = statement.executeUpdate();
        return i;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        //释放资源
        DBUtils.closeResources(connection,statement,resultSet);
    }
}
public int updateScore(Score scores){
    //通过学生id修改学生信息
    try {
        int id= scores.getStudentId();
        int courseId=scores.getCouseId();
                BigDecimal score=scores.getScore();
        connection= DBUtils.getConnection();
        //创建sql
        String sql="update score set score=? where student_id=? and course_id=?";
        //创建statement
        statement=connection.prepareStatement(sql);
        //补齐参数
        statement.setBigDecimal(1,score);
        statement.setInt(2,id);
        statement.setInt(3,courseId);
        //执行并返回结果
        int i = statement.executeUpdate();
        return i;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
public int delete(Score score){
    //建立连接
    try {

        connection=DBUtils.getConnection();
        //定义sql语句
        String sql="delete from score where student_id=? and course_id=?";
        statement=connection.prepareStatement(sql);
        int id=score.getStudentId();
        int course_id=score.getCouseId();
        //补齐sql
        statement.setInt(1,id);
        statement.setInt(2,course_id);
        int i = statement.executeUpdate();
        return i;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        DBUtils.closeResources(connection,statement,resultSet);
    }
}
    public List<Score> deleteAll(int classId){
    //建立连接
        try {
            connection=DBUtils.getConnection();
            //编写sql
            String sql="select cou.name,stu.name ,sco.score from course cou,student stu,score sco, classes cls where sco.student_id=stu.id and sco.course_id =cou.id and stu.classes_id=cls.id and cls.id=?";
            //创建执行器
            statement=connection.prepareStatement(sql);
            //补全sql
            statement.setInt(1,classId);
            //开始执行
            resultSet = statement.executeQuery();
            ArrayList<Score>list=null;
            while(resultSet.next()){
                if(list==null){
                    list=new ArrayList<>();
                }
                Score score = new Score();
                Course course = new Course();
                course.setName(resultSet.getString(1));
                Student student = new Student();
                student.setName(resultSet.getString(2));
                score.setStudent(student);
                score.setCourse(course);
                score.setScore(resultSet.getBigDecimal(3));
                list.add(score);
            }
            return list;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //释放资源
            DBUtils.closeResources(connection,statement,resultSet);
        }
    }

}
